﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExcelAddIn
{
    public partial class GZBHZ : Form
    {
        Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
        public GZBHZ()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 求和弹出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonSum_Click(object sender, EventArgs e)
        {
            var selectRang = app.InputBox("要汇总的区域", "请选择需要汇总的单元格，按住Ctrl键可多选", Type: 8);
            if (selectRang is Range)
            {
                InputeSum.Text = ((Range)selectRang).Address;
            }
        }
        /// <summary>
        /// 平均弹出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonAvg_Click(object sender, EventArgs e)
        {
            var selectRang = app.InputBox("要汇总的区域", "请选择需要求平均值的单元格，按住Ctrl键可多选", Type: 8);
            if (selectRang is Range)
            {
                InputeAvg.Text = ((Range)selectRang).Address[External: true];
            }
        }

        /// <summary>
        /// 确认
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonOk_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(InputeAvg.Text))
                AvgAllSheet(InputeAvg.Text);
            if (!string.IsNullOrEmpty(InputeSum.Text))
                SumAllSheet(InputeSum.Text);
        }
        private void SumAllSheet(string allRange)
        {
            double allSum = 0;
            double cellValue = 0;
            //获取所有选中区域
            Range range = ((Worksheet)(app.ActiveSheet)).get_Range(allRange);
            //循环所有选中区域
            foreach (Range area in range.Cells)
            {
                allSum = 0;
                string rangText = area.Address;
                foreach (var sheetName in checkedListBoXAllSheet.CheckedItems)
                {
                    range = ((Worksheet)app.Sheets[sheetName]).get_Range(rangText);//获取表格里面特定范围
                    foreach (Range cell in range.Cells)
                    {
                        if (cell.Value is Double)
                            allSum += cell.Value;
                        else if (cell.Value is String && !string.IsNullOrEmpty(cell.Value))
                        {
                            Double.TryParse(cell.Value, out cellValue);
                            //说明不是数字
                            if (cellValue == 0 && !string.IsNullOrEmpty(cell.Value) && cell.Value != "0")
                                allSum++;
                            else if (cellValue != 0)//是数字
                                allSum += cellValue;
                        }
                    }
                }
                ((Worksheet)app.ActiveSheet).Range[rangText].Value = allSum;
            }
        }
        private void AvgAllSheet(string allRange)
        {
            double allSum = 0;
            int count = 0;
            double cellValue = 0;
            //获取所有选中区域
            Range range = ((Worksheet)(app.ActiveSheet)).get_Range(allRange);
            //循环所有选中区域
            foreach (Range area in range.Cells)
            {
                allSum = count = 0;
                string rangText = area.Address;
                foreach (var sheetName in checkedListBoXAllSheet.CheckedItems)
                {
                    range = ((Worksheet)app.Sheets[sheetName]).get_Range(rangText);//获取表格里面特定范围
                    foreach (Range cell in range.Cells)
                    {
                        if (cell.Value is Double)
                        {
                            allSum += cell.Value;
                            count++;
                        }
                        else if (cell.Value is String && !string.IsNullOrEmpty(cell.Value))//是字符串，且不为空
                        {
                            Double.TryParse(cell.Value, out cellValue);
                            //说明是数字
                            if (cellValue != 0 || cell.Value == "0")
                            {
                                allSum += cellValue;
                                count++;
                            }
                        }

                    }
                }
                if (count > 0)
                    ((Worksheet)app.ActiveSheet).Range[rangText].Value = allSum / count;
            }
        }

        /// <summary>
        /// 加载事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void GZBHZ_Load(object sender, EventArgs e)
        {
            #region 所有Sheets绑定到checkedListBox
            var allSheets = app.Sheets;
            List<string> source = new List<string>();
            for (int i = 1; i <= allSheets.Count; i++)
            {
                source.Add(allSheets[i].Name);
            }
            checkedListBoXAllSheet.DataSource = source;
            //默认选中所有
            for (int i = 0; i < checkedListBoXAllSheet.Items.Count; i++)
            {
                checkedListBoXAllSheet.SetItemChecked(i, true);
            }
            #endregion
        }

        /// <summary>
        /// 全选按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void checkBox_CheckAll_CheckedChanged(object sender, EventArgs e)
        {
            bool isCheckedAll = checkBox_CheckAll.Checked;
            for (int i = 0; i < checkedListBoXAllSheet.Items.Count; i++)
            {
                checkedListBoXAllSheet.SetItemChecked(i, isCheckedAll);
            }
        }
    }
}
